package warehouse;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StaffBean {
	private int staffNo = 0;
	private String name = "";
	private boolean gender = false;//true为男
	private int age = 0;
	private String telenumber = "";
	private int warehouseNo = 0;
	
	public StaffBean() {
		
	}

	public StaffBean(int staffNo, String name, boolean gender, int age, String telenumber, int warehouseNo) {
		super();
		this.staffNo = staffNo;
		this.name = name;
		this.gender = gender;
		this.age = age;
		this.telenumber = telenumber;
		this.warehouseNo = warehouseNo;
	}
	
	/**
	 * 添加职工
	 * @param staff 职工信息
	 * @return 添加后获得的职工号，若为-1，则添加失败
	 */
	public static int addStaff(StaffBean staff) {
		Connection connection = DatabaseBean.connect();
		
		String sql = "INSERT INTO staff(name,gender,age,telenumber,warehouseNo) VALUES(?,?,?,?,?)";
		PreparedStatement ps=null;
		try {
			ps=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, staff.getName());
			ps.setString(2,staff.isGender()?"M":"F");
			ps.setInt(3, staff.getAge());
			ps.setString(4, staff.getTelenumber());
			ps.setInt(5, staff.getWarehouseNo());
			
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();//获取自增主键
			int staffNo=-1;//保存生成的编号
			if (rs != null&&rs.next()) {  
				staffNo=rs.getInt(1); 
			}
			return staffNo;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return -1;
			}
		}
		
		
	}
	
	public static boolean modStaff(StaffBean staff) {
		Connection connection = DatabaseBean.connect();
		
		String sql = "UPDATE staff SET name=?,gender=?,age=?,telenumber=?,warehouseNo=? WHERE staffNo=?";
		PreparedStatement ps=null;
		try {
			ps=connection.prepareStatement(sql);
			ps.setString(1, staff.getName());
			ps.setString(2,staff.isGender()?"M":"F");
			ps.setInt(3, staff.getAge());
			ps.setString(4, staff.getTelenumber());
			ps.setInt(5, staff.getWarehouseNo());
			ps.setInt(6, staff.getStaffNo());
			
			ps.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		
	}

	public static boolean delStaff(int staffNo) {
		Connection connection = DatabaseBean.connect();
		
		String sql = "DELETE FROM staff WHERE staffNo=?";
		PreparedStatement ps=null;
		try {
			ps=connection.prepareStatement(sql);
			
			ps.setInt(1, staffNo);
			
			ps.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		
	}
	/**
	 * 读取职工信息
	 * @param staffNo
	 * @return 带有职工信息的对象，若为null则找不到
	 */
	public static StaffBean queryStaff(int staffNo) {
		StaffBean staff = null;
		Connection connection = DatabaseBean.connect();
		
		String sql = "SELECT * FROM staff WHERE staffNo=?";
		PreparedStatement ps=null;
		try {
			ps=connection.prepareStatement(sql);
			
			ps.setInt(1, staffNo);
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()) {
				String name = rs.getString("name");
				boolean gender = "M".equals(rs.getString("gender"))?true:false;
				int age = rs.getInt("age");
				String telenumber = rs.getString("telenumber");
				int warehouseNo = rs.getInt("warehouseNo");
				
				staff = new StaffBean(staffNo, name, gender, age, telenumber, warehouseNo);
				
			}

			return staff;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return staff;
			}
		}
	}
	
	
	public static void main(String[] args) {
		StaffBean staff = new StaffBean(0, "李刚", true, 24, "15902919991", 1);
		
		int staffNo = StaffBean.addStaff(staff);
		System.out.println(staffNo);
		
		staff.setStaffNo(staffNo);
		staff.setAge(30);
		boolean success = StaffBean.modStaff(staff);
		System.out.println(success);
		
		success = StaffBean.delStaff(staffNo);
		System.out.println(success);
		
	}
	
	//-------------------------
	public int getStaffNo() {
		return staffNo;
	}

	public void setStaffNo(int staffNo) {
		this.staffNo = staffNo;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public boolean isGender() {
		return gender;
	}

	public void setGender(boolean gender) {
		this.gender = gender;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getTelenumber() {
		return telenumber;
	}

	public void setTelenumber(String telenumber) {
		this.telenumber = telenumber;
	}

	public int getWarehouseNo() {
		return warehouseNo;
	}

	public void setWarehouseNo(int warehouseNo) {
		this.warehouseNo = warehouseNo;
	}

	 
}
